﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Formatters.Xml;
using Microsoft.Extensions.PlatformAbstractions;
using KeepAccounts.Common.Utilities;
using KeepAccounts.Models.Base;
using KeepAccounts.Models.Swagger;  
using MySql.Data.MySqlClient;

namespace ShoesApp.Api.Controllers
{
    /// <summary>
    /// 数据库相关
    /// </summary>
    [ApiExplorerSettings(GroupName = Grouping.V1)]
    [Route("Common/[controller]")]
    [ApiController]
    public class DataBaseController : ControllerBase
    {


        /// <summary>
        /// 生成表文档
        /// </summary>
        /// <returns></returns>
        [HttpGet(nameof(Tables))]
        public async Task Tables()
        {
            StringBuilder htmlStringBuilder = new StringBuilder();

            var accept = Request.GetTypedHeaders().Accept;

            htmlStringBuilder.Append("<html>");
            htmlStringBuilder.Append("<head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /> <title>数据库表结构</title></head>");//支持中文
            htmlStringBuilder.Append("<body>");



            //htmlStringBuilder.Append("<spen style=\"font-size: 300%\">");//让字体变大

            //htmlStringBuilder.Append("</spen>");




            var typeList = new List<Type>() {
    //        typeof(KeepAccounts.Models.System.SystemDepartmentEntity ),
    //         typeof(Models.System.SystemDepartmentRoleEntity  ),
    //             typeof(Models.System.SystemDepartmentStoreEntity  ),
    //                typeof(Models.System.SystemLogEntity  ),
    //                    typeof(Models.System.SystemMenuEntity  ),
    //                            typeof(Models.System.SystemModuleEntity  ),
    //typeof(Models.System.SystemRoleEntity  ),
    //            typeof(Models.System.SystemRoleMenuEntity  ),
    //               typeof( Models.Models.PayLog.PayLogEntity  ),
    //            typeof( Models.Models.UserInfo.UserAddressEntity ),

            };


            var basetype = typeof(BaseEntity);
            var types = typeof(BaseEntity).Assembly.GetTypes().Where(t => basetype.IsAssignableFrom(t) && !t.IsAbstract).ToList();
            var modelDescriptions = GetModelDescription();


            typeList.ForEach(item => { types.Remove(item); });

            var theader = $" <tr class=\"theader\" > <td> 字段名 </td> <td> 类型</td>  <td> 备注 </td></tr>";

            var tables = types.Select(
              type =>
              {
                  if (typeList.Contains(type))
                  {

                      return "";
                  }



                  var att = type.GetCustomAttribute<TableAttribute>();
                  var tName = att?.Name ?? type.Name.Replace("Entity", string.Empty);

                  var properties = type.GetProperties().Select(p =>
                  {
                      var name = $"{p.DeclaringType.FullName}.{p.Name}";
                      var modelDescription = modelDescriptions.FirstOrDefault(t => t.Item1.Trim() == name);

                      var col = p;
                      var pName = string.IsNullOrEmpty(col?.Name) ? p.Name : col?.Name;
                      var ptype = p.PropertyType;
                      var property = $" <tr> <td> {pName} </td> <td> {GetSqlProperty(ptype)}</td>  <td> {modelDescription?.Item2}</td></tr>";

                      return property;
                  }).Where(s => !string.IsNullOrEmpty(s));

                  var tableDescription = modelDescriptions.FirstOrDefault(t => t.Item1.Trim() == $"{type.FullName}");

                  var sql = $" <div class=\"item\" > {types.IndexOf(type) + 1}. {tName} ({tableDescription?.Item2}) <table> {theader} {string.Join(" ", properties)}  </table></div>";
                  return sql;

              });
            // return string.Join(" ", tables);



            string str = string.Join(" ", tables);


            htmlStringBuilder.Append("<div class=\"content\" >");

            htmlStringBuilder.Append(str);

            htmlStringBuilder.Append("</div>");

            htmlStringBuilder.Append("<style>");

            htmlStringBuilder.Append("*{margin: 0;padding: 0; }");

            htmlStringBuilder.Append(".content{width: 700px;margin:0 auto; }");
            htmlStringBuilder.Append(".item{margin-top:20px }");

            htmlStringBuilder.Append("table{margin-top:5px; width: 100%;cellpadding:0; cellspacing:0;border-collapse: collapse;}");
            htmlStringBuilder.Append(".theader{background: #12a086;color: #fff;}");

            htmlStringBuilder.Append("td{border: 1px solid #808080;width:30%;padding:2px 5px; }");
            htmlStringBuilder.Append("</style>");



            htmlStringBuilder.Append("</body>");
            htmlStringBuilder.Append("</html>");



            var data = Encoding.UTF8.GetBytes(htmlStringBuilder.ToString());

            if (accept.Any(x => x.MediaType == "text/html"))
            {
                Response.ContentType = "text/html";
            }
            else
            {
                Response.ContentType = "text/plain";
            }
            Response.StatusCode = 200;
            await Response.Body.WriteAsync(data, 0, data.Length);

        }


        /// <summary>
        /// 生成创建表Sql
        /// </summary>
        /// <returns></returns>
        [HttpPost("CreateTableScript")]
        public string CreateTableScript()
        {
            var basetype = typeof(BaseEntity);



            var types = typeof(BaseEntity).Assembly.GetTypes().Where(t => basetype.IsAssignableFrom(t) && !t.IsAbstract).ToList();
            var currents = CurrentTableByMySql();
            var tables = types.Select(
                type =>
                {

                    var att = type.GetCustomAttribute<TableAttribute>();
                    var tName = att?.Name ?? type.Name.Replace("Entity", string.Empty);
                    if (currents.Any(s => s.Equals(tName.ToLower())))
                    {
                        var columns = TableColumnsByMySql(tName);
                        List<string> changes = new List<string> { };
                        var ps = new List<string> { };
                        type.GetProperties().ToList().ForEach(p =>
                        {
                            var col = p;
                            var pName = string.IsNullOrEmpty(col?.Name) ? p.Name : col?.Name;
                            ps.Add(pName.ToLower());
                            if (!columns.Any(c => c.Equals(pName.ToLower())))
                            {
                                var ptype = p.PropertyType;
                                var property = $" ALTER TABLE {tName} ADD {pName} {GetSqlPropertyMySql(ptype)}; ";
                                changes.Add(property);
                            }
                        });
                        columns.ForEach(c =>
                        {
                            if (ps.Contains(c))
                            {
                                return;
                            }
                            var sql = $" alter table {tName} drop column {c}; ";
                            changes.Add(sql);
                        });
                        return string.Join(string.Empty, changes);
                    }
                    else
                    {
                        var properties = type.GetProperties().Select(p =>
                        {
                            var col = p;
                            var pName = string.IsNullOrEmpty(col?.Name) ? p.Name : col?.Name;
                            var ptype = p.PropertyType;
                            var property = $" {pName} {GetSqlPropertyMySql(ptype)} ";
                            if (pName.ToLower().Equals("id"))
                            {
                                property += " primary key ";
                            }
                            return property;
                        }).Where(s => !string.IsNullOrEmpty(s));
                        var sql = $"Create table {tName} ( {string.Join(",", properties)} );";
                        return sql;
                    }

                });
            return string.Join(" ", tables);
        }

        /// <summary>
        /// 生成修改表结构Sql
        /// </summary>
        /// <returns></returns>
        [HttpPost("UpdateTableDescription")]
        public string UpdateTableDescription()
        {
            var basetype = typeof(BaseEntity);
            var types = typeof(BaseEntity).Assembly.GetTypes().Where(t => basetype.IsAssignableFrom(t) && !t.IsAbstract).ToList();
            var currents = CurrentTable();
            var modelDescriptions = GetModelDescription();
            StringBuilder stringBuilder = new StringBuilder();
            types.ForEach(
               type =>
               {
                   var att = type.GetCustomAttribute<TableAttribute>();
                   var tName = att?.Name ?? type.Name.Replace("entity", string.Empty);
                   List<string> changes = new List<string> { };
                   type.GetProperties().ToList().ForEach(p =>
                   {
                       var name = $"{p.DeclaringType.FullName}.{p.Name}";
                       var modelDescription = modelDescriptions.FirstOrDefault(t => t.Item1.Trim() == name);
                       if (modelDescription != null)
                       {
                           var sql = GetSqlDescription(tName, p.Name, modelDescription.Item2);
                           stringBuilder.Append(sql);
                       }
                   });
                   var tableDescription = modelDescriptions.FirstOrDefault(t => t.Item1.Trim() == $"{type.FullName}");
                   if (tableDescription != null)
                   {
                       var sql = GetSqlTabelDescription(tName, tableDescription.Item2);
                       stringBuilder.Append(sql);
                   }
               });
            return stringBuilder.ToString();
        }

        private List<Tuple<string, string>> GetModelDescription()
        {
            List<Tuple<string, string>> tuples = new List<Tuple<string, string>>();
            var basePath = PlatformServices.Default.Application.ApplicationBasePath; ;
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(Path.Combine(basePath, "ShoesApp.Models.xml"));
            XmlNode rootNode = xmlDoc.SelectSingleNode("doc");
            XmlNode membersNode = rootNode.SelectSingleNode("members");
            foreach (XmlNode node in membersNode.ChildNodes)
            {
                string description = node.InnerText.Trim();
                string nameProperty = node.Attributes["name"]?.InnerText;
                if (!string.IsNullOrEmpty(nameProperty))
                {
                    var propertyName = nameProperty.Split(":")[1];
                    tuples.Add(new Tuple<string, string>(propertyName, description));
                }
            }
            return tuples;
        }


        #region CreateTableScript
        private List<string> CurrentTable()
        {
            var sql = "select* from sys.tables";
            var table = ExecuteTable(sql);
            var query = from DataRow row in table.Rows
                        select row[0].ToString().ToLower();
            return query.ToList();
        }

        private List<string> CurrentTableByMySql()
        {
            var sql = "select table_name from information_schema.tables  WHERE table_schema='ShoesApp'";
            var table = ExecuteTableByMySql(sql);
            var query = from DataRow row in table.Rows
                        select row[0].ToString().ToLower();
            return query.ToList();
        }

        private List<string> TableColumns(string table)
        {
            var sql = $" sp_columns [{table}]";
            var data = ExecuteTable(sql);
            var query = from DataRow row in data.Rows
                        select row[3].ToString().ToLower();
            return query.ToList();
        }

        private List<string> TableColumnsByMySql(string table)
        {
            var sql = $" select * from information_schema.COLUMNS where table_schema='ShoesApp' AND table_name = '{table}';  ";
            var data = ExecuteTableByMySql(sql);
            var query = from DataRow row in data.Rows
                        select row[3].ToString().ToLower();
            return query.ToList();
        }

        private string GetSqlProperty(Type type)
        {
            if (type == typeof(string))
            {
                return "nvarchar(500)";
            }
            else if (type == typeof(int))
            {
                return "int";
            }
            else if (type == typeof(decimal))
            {
                return "decimal(18,6)";
            }
            else if (type == typeof(DateTime))
            {
                return "datetime";
            }
            else if (type == typeof(bool))
            {
                return "bit";
            }
            else if (type == typeof(Guid))
            {
                return "uniqueidentifier";
            }
            return "unknow";
        }

        private string GetSqlPropertyMySql(Type type)
        {
            if (type == typeof(string))
            {
                return "varchar(500)";
            }
            else if (type == typeof(int))
            {
                return "int";
            }
            else if (type == typeof(long))
            {
                return "BIGINT";
            }
            else if (type == typeof(decimal))
            {
                return "decimal(18,6)";
            }
            else if (type == typeof(DateTime))
            {
                return "datetime";
            }
            else if (type == typeof(bool))
            {
                return "bit";
            }
            else if (type == typeof(Guid))
            {
                return "varchar(50)";
            }
            else if (type == typeof(byte[]))
            {
                //1、binary和varbinary，适合存储少量的二进制数据
                //2、blob适合存储大量的数据
                return "blob";
            }
            return "unknow";
        }

        public static DataTable ExecuteTable(string cmdtext)
        {
            var sqlConstr = ManagerConfig.GetManagerConfig("ConnectionStrings:MySql");
            //using (SqlConnection con = new SqlConnection(sqlConstr))
            //{
            //    con.Open();
            //    using (SqlCommand cmd = new SqlCommand(cmdtext, con))
            //    {
            //        SqlDataAdapter sda = new SqlDataAdapter(cmd);
            //        DataTable dt = new DataTable();
            //        sda.Fill(dt);
            //        return dt;
            //    }
            //}
            return null;
        }

        public static DataTable ExecuteTableByMySql(string cmdtext)
        {
            var sqlConstr = ManagerConfig.GetManagerConfig("ConnectionStrings:MySql");
            using (MySqlConnection con = new MySqlConnection(sqlConstr))
            {
                con.Open();
                using (MySqlCommand cmd = new MySqlCommand(cmdtext, con))
                {
                    MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
            }
        }



        private string GetSqlDescription(string tableName, string propertyName, string description)
        {
            var sql = $"if not exists(SELECT C.value AS column_description FROM sys.tables A INNER JOIN sys.columns B ON B.object_id = A.object_id INNER JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id WHERE A.name = N'{tableName}' and B.name = N'{propertyName}') EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{description}' , @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'{tableName}', @level2type = N'COLUMN', @level2name = N'{propertyName}';" +
                $" EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'{description}' , @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'{tableName}', @level2type = N'COLUMN', @level2name = N'{propertyName}';";
            return sql;
        }

        private string GetSqlTabelDescription(string tableName, string description)
        {
            var sql = $"if not exists (SELECT A.name,C.value FROM sys.tables A inner JOIN sys.extended_properties C ON C.major_id = A.object_id  and minor_id = 0 WHERE A.name = N'{tableName}') EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{description}' , @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'{tableName}';" +
                $" EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'{description}' , @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'{tableName}';";
            return sql;
        }


        #endregion

    }
}